/*
 * 
 * Copyright (C) 2008 VirtualStaticVoid <virtualstaticvoid@gmail.com>
 * 
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http: *www.gnu.org/licenses/>.
 *
*/

use AomDB
go

create schema aom
go

create schema aomgen
go

create table aom.EntityType
(
  Id int identity(1, 1) not null
    constraint pk_EntityType primary key,

  Version rowversion not null,

  Name nvarchar(50) not null
    constraint uk_EntityType unique,
  
  TypeCode int not null
    constraint df_EntityType_TypeCode default (0),
  
)
go

create table aom.Entity
(
  Id int identity(1, 1) not null
    constraint pk_Entity primary key,

  Version rowversion not null,

  EntityTypeId int not null
    constraint fk_Entity_EntityType_EntityTypeId foreign key
      references aom.EntityType ( Id ),

  Name nvarchar(50) not null,

  UserDefined bit not null
    constraint df_Entity_UserDefined default (1),

  constraint uk_Entity unique
  (
    EntityTypeId,
    Name 
  ),
  
)
go

create function aom.fn_getEntityTypeId
(
  @i_entityid int
)
returns int
as
begin

  declare @entitytypeid int

  select @entitytypeid = EntityTypeId
  from aom.Entity
  where Id = @i_entityid

  return @entitytypeid
  
end
go

create table aom.EntityAttributeType
(
  Id int identity(1, 1) not null
    constraint pk_EntityAttributeType primary key,

  Version rowversion not null,

  EntityTypeId int not null
    constraint fk_EntityAttributeType_EntityType_EntityTypeId foreign key
      references aom.EntityType ( Id ),

  Name nvarchar(50) not null,

  DataType int not null
    constraint ck_EntityAttributeType_DataType 
      check ( DataType in ( 
                            0,        -- bit
                            1,        -- int
                            2,        -- decimal
                            3,        -- datetime
                            4,        -- string
                            5,        -- entity
                            6,        -- xml
                            7         -- enum
                           ) 
            ),
  
  --DefaultData xml null,                    -- TODO: XML representation of a default value

  UserDefined bit not null
    constraint df_EntityAttributeType_UserDefined default (1),

  -- only when DataType == Entity
  DataEntityTypeId int null
    constraint fk_EntityAttributeType_EntityType_DataEntityTypeId foreign key
      references aom.EntityType ( Id ),

  constraint ck_EntityAttributeType_DataEntityTypeId check
  (
    ( case DataType 
          when 6 then ( case when DataEntityTypeId is null then 1 else 0 end )
      else 0
    end ) = 0
  ),

  constraint uk_EntityAttributeType unique
  (
    EntityTypeId,
    Name
  ),

)
go

create function aom.fn_getEntityAttributeTypeEntityTypeId
(
  @i_entityattributetypeid int
)
returns int
as
begin

  declare @entitytypeid int

  select @entitytypeid = EntityTypeId
  from aom.EntityAttributeType
  where Id = @i_entityattributetypeid

  return @entitytypeid
  
end
go

create function aom.fn_getEntityAttributeTypeDataEntityTypeId
(
  @i_entityattributetypeid int
)
returns int
as
begin

  declare @dataentitytypeid int

  select @dataentitytypeid = DataEntityTypeId
  from aom.EntityAttributeType
  where Id = @i_entityattributetypeid

  return @dataentitytypeid
  
end
go

create table aom.EntityAttributeBool
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeBool_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeBool_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data bit null,
  
  constraint ck_EntityAttributeBool_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),
  
  constraint pk_EntityAttributeBool primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeBool
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns bit
as
begin

  declare @r bit

  select @r = Data
  from aom.EntityAttributeBool
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeBool
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data bit
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeBool
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeBool
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeBool ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeInt
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeInt_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeInt_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data int null,
  
  constraint ck_EntityAttributeInt_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),

  constraint pk_EntityAttributeInt primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeInt
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns int
as
begin

  declare @r int

  select @r = Data
  from aom.EntityAttributeInt
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeInt
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data int
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeInt
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeInt
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeInt ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeEnum
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeEnum_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeEnum_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data int not null,
  
  constraint ck_EntityAttributeEnum_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),

  constraint pk_EntityAttributeEnum primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeEnum
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns int
as
begin

  declare @r int

  select @r = Data
  from aom.EntityAttributeEnum
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeEnum
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data bit
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeEnum
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeEnum
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeEnum ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeDecimal
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeDecimal_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeDecimal_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data decimal(38, 15) null,
  
  constraint ck_EntityAttributeDecimal_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),
  
  constraint pk_EntityAttributeDecimal primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeDecimal
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns decimal(38, 15)
as
begin

  declare @r decimal(38, 15)

  select @r = Data
  from aom.EntityAttributeDecimal
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeDecimal
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data decimal(38, 15)
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeDecimal
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeDecimal
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeDecimal ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeDateTime
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeDateTime_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeDateTime_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data datetime null,
  
  constraint ck_EntityAttributeDateTime_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),
  
  constraint pk_EntityAttributeDateTime primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeDateTime
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns datetime
as
begin

  declare @r datetime

  select @r = Data
  from aom.EntityAttributeDateTime
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeDateTime
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data datetime
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeDateTime
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeDateTime
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeDateTime ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeString
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeString_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeString_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data nvarchar(255) null,
  
  constraint ck_EntityAttributeString_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),
  
  constraint pk_EntityAttributeString primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeString
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns nvarchar(255)
as
begin

  declare @r nvarchar(255)

  select @r = Data
  from aom.EntityAttributeString
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeString
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data nvarchar(255)
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeString
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeString
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeString ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeEntity
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeEntity_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeEntity_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data int null
    constraint fk_EntityAttributeEntity_Entity_Data foreign key
      references aom.Entity ( Id ),
  
  constraint ck_EntityAttributeEntity_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),
  
  constraint ck_EntityAttributeEntity_Data check
  (
    aom.fn_getEntityTypeId(Data) = aom.fn_getEntityAttributeTypeDataEntityTypeId(EntityAttributeTypeId)
  ),

  constraint pk_EntityAttributeEntity primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeEntity
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns int
as
begin

  declare @r int

  select @r = Data
  from aom.EntityAttributeEntity
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeEntity
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data int
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeEntity
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeEntity
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeEntity ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go

create table aom.EntityAttributeXml
(

  EntityAttributeTypeId int not null
    constraint fk_EntityAttributeXml_EntityAttributeType_EntityAttributeTypeId foreign key
      references aom.EntityAttributeType ( Id ),

  EntityId int not null
    constraint fk_EntityAttributeXml_Entity_EntityId foreign key
      references aom.Entity ( Id ),

  Data xml null,
  
  constraint ck_EntityAttributeXml_EntityTypeMatch check
  (
    aom.fn_getEntityTypeId(EntityId) = aom.fn_getEntityAttributeTypeEntityTypeId(EntityAttributeTypeId)
  ),
  
  constraint pk_EntityAttributeXml primary key
  (
    EntityAttributeTypeId,
    EntityId
  ),

)
go

create function aom.fn_getEntityAttributeXml
(
  @i_entityattributetypeid int,
  @i_entityid int  
)
returns xml
as
begin

  declare @r xml

  select @r = Data
  from aom.EntityAttributeXml
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  return @r

end
go

create procedure aom.fn_setEntityAttributeXml
(
  @i_entityattributetypeid int,
  @i_entityid int,
  @i_data xml
)
as

  if ( @i_data is null )
  begin

    delete aom.EntityAttributeXml
    where EntityAttributeTypeId = @i_entityattributetypeid
      and EntityId = @i_entityid

    return @@error

  end

  update aom.EntityAttributeXml
  set Data = @i_data
  where EntityAttributeTypeId = @i_entityattributetypeid
    and EntityId = @i_entityid

  if ( @@rowcount = 0 )

    insert into aom.EntityAttributeXml ( EntityAttributeTypeId, EntityId, Data )
    values ( @i_entityattributetypeid, @i_entityid, @i_data )

  return @@error

go


create table aom.RelationType
(
  Id int identity(1, 1) not null
    constraint pk_RelationType primary key,

  Version rowversion not null,

  LeftEntityTypeId int not null
    constraint fk_AttributeType_EntityType_LeftEntityTypeId foreign key
      references aom.EntityType ( Id ),

  RightEntityTypeId int not null
    constraint fk_AttributeType_EntityType_RightEntityTypeId foreign key
      references aom.EntityType ( Id ),

  Name nvarchar(50) not null,

  TypeCode int not null
    constraint df_RelationType_TypeCode default (0),

  constraint uk_RelationType unique
  (
    LeftEntityTypeId,
    RightEntityTypeId,
    Name
  ),
  
)
go

create table aom.Relation
(
  Id int identity(1, 1) not null
    constraint pk_Relation primary key,

  Version rowversion not null,

  RelationTypeId int not null
    constraint fk_Relation_RelationType_RelationTypeId foreign key
      references aom.RelationType ( Id ),

  LeftEntityId int not null
    constraint fk_Attribute_Entity_LeftEntityId foreign key
      references aom.Entity ( Id ),

  RightEntityId int not null
    constraint fk_Attribute_Entity_RightEntityId foreign key
      references aom.Entity ( Id ),

  UserDefined bit not null
    constraint df_Relation_UserDefined default (1),

  constraint uk_Relation unique
  (
    RelationTypeId,
    LeftEntityId,
    RightEntityId
  ),
  
)
go


create function aom.fn_getRelationTypeId
(
  @i_relationid int
)
returns int
as
begin

  declare @relationtypeid int

  select @relationtypeid = RelationTypeId
  from aom.Relation
  where Id = @i_relationid

  return @relationtypeid
  
end
go

create table aom.RelationAttributeType
(
  Id int identity(1, 1) not null
    constraint pk_RelationAttributeType primary key,

  Version rowversion not null,

  RelationTypeId int not null
    constraint fk_RelationAttributeType_RelationType_RelationTypeId foreign key
      references aom.RelationType ( Id ),

  Name nvarchar(50) not null,

  DataType int not null
    constraint ck_RelationAttributeType_DataType 
      check ( DataType in ( 
                            0,        -- bit
                            1,        -- int
                            2,        -- decimal
                            3,        -- datetime
                            4,        -- string
                            5,        -- entity
                            6,        -- xml
                            7         -- enum
                           ) 
            ),
  
  --DefaultData xml null,                    -- TODO: XML representation of a default value

  UserDefined bit not null
    constraint df_RelationAttributeType_UserDefined default (1),

  -- only when DataType == Entity
  DataEntityTypeId int null
    constraint fk_RelationAttributeType_EntityType_DataEntityTypeId foreign key
      references aom.EntityType ( Id ),

  constraint ck_RelationAttributeType_DataEntityTypeId check
  (
    ( case DataType 
          when 6 then ( case when DataEntityTypeId is null then 1 else 0 end )
      else 0
    end ) = 0
  ),

  constraint uk_RelationAttributeType unique
  (
    RelationTypeId,
    Name
  ),

)
go

create function aom.fn_getRelationAttributeTypeRelationTypeId
(
  @i_relationattributetypeid int
)
returns int
as
begin

  declare @relationtypeid int

  select @relationtypeid = RelationTypeId
  from aom.RelationAttributeType
  where Id = @i_relationattributetypeid

  return @relationtypeid
  
end
go

create function fn_getRelationAttributeTypeRelationTypeId
(
  @i_relationattributetypeid int
)
returns int
as
begin

  declare @relationtypeid int

  select @relationtypeid = RelationTypeId
  from aom.RelationAttributeType
  where Id = @i_relationattributetypeid

  return @relationtypeid
  
end
go

create function aom.fn_getRelationAttributeTypeDataEntityTypeId
(
  @i_relationattributetypeid int
)
returns int
as
begin

  declare @dataentitytypeid int

  select @dataentitytypeid = DataEntityTypeId
  from aom.RelationAttributeType
  where Id = @i_relationattributetypeid

  return @dataentitytypeid
  
end
go

create table aom.RelationAttributeBool
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeBool_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeBool_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data bit null,
  
  constraint ck_RelationAttributeBool_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeBool primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeBool
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns bit
as
begin

  declare @r bit

  select @r = Data
  from aom.RelationAttributeBool
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeBool
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data bit
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeBool
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeBool
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeBool ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeInt
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeInt_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeInt_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data int null,
  
  constraint ck_RelationAttributeInt_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeInt primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeInt
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns int
as
begin

  declare @r int

  select @r = Data
  from aom.RelationAttributeInt
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeInt
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data int
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeInt
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeInt
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeInt ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeEnum
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeEnum_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeEnum_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data int not null,
  
  constraint ck_RelationAttributeEnum_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeEnum primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeEnum
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns int
as
begin

  declare @r int

  select @r = Data
  from aom.RelationAttributeEnum
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeEnum
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data bit
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeEnum
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeEnum
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeEnum ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeDecimal
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeDecimal_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeDecimal_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data decimal(38, 15) null,
  
  constraint ck_RelationAttributeDecimal_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeDecimal primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeDecimal
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns decimal(38, 15)
as
begin

  declare @r decimal(38, 15)

  select @r = Data
  from aom.RelationAttributeDecimal
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeDecimal
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data decimal(38, 15)
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeDecimal
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeDecimal
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeDecimal ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeDateTime
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeDateTime_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeDateTime_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data datetime null,
  
  constraint ck_RelationAttributeDateTime_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeDateTime primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeDateTime
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns datetime
as
begin

  declare @r datetime

  select @r = Data
  from aom.RelationAttributeDateTime
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeDateTime
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data datetime
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeDateTime
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeDateTime
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeDateTime ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeString
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeString_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeString_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data nvarchar(255) null,
  
  constraint ck_RelationAttributeString_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeString primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeString
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns nvarchar(255)
as
begin

  declare @r nvarchar(255)

  select @r = Data
  from aom.RelationAttributeString
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeString
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data nvarchar(255)
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeString
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeString
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeString ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeEntity
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeEntity_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeEntity_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data int null
    constraint fk_RelationAttributeEntity_Relation_Data foreign key
      references aom.Relation ( Id ),
  
  constraint ck_RelationAttributeEntity_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),

  constraint ck_RelationAttributeEntity_Data check
  (
    aom.fn_getEntityTypeId(Data) = aom.fn_getRelationAttributeTypeDataEntityTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeEntity primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeEntity
(
  @i_entityattributetypeid int,
  @i_relationid int
)
returns int
as
begin

  declare @r int

  select @r = Data
  from aom.RelationAttributeEntity
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeEntity
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data int
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeEntity
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeEntity
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeEntity ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go

create table aom.RelationAttributeXml
(

  RelationAttributeTypeId int not null
    constraint fk_RelationAttributeXml_RelationAttributeType_RelationAttributeTypeId foreign key
      references aom.RelationAttributeType ( Id ),

  RelationId int not null
    constraint fk_RelationAttributeXml_Relation_RelationId foreign key
      references aom.Relation ( Id ),

  Data xml null,
  
  constraint ck_RelationAttributeXml_RelationTypeMatch check
  (
    aom.fn_getRelationTypeId(RelationId) = aom.fn_getRelationAttributeTypeRelationTypeId(RelationAttributeTypeId)
  ),
  
  constraint pk_RelationAttributeXml primary key
  (
    RelationAttributeTypeId,
    RelationId
  ),

)
go

create function aom.fn_getRelationAttributeXml
(
  @i_entityattributetypeid int,
  @i_relationid int  
)
returns xml
as
begin

  declare @r xml

  select @r = Data
  from aom.RelationAttributeXml
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  return @r

end
go

create procedure aom.fn_setRelationAttributeXml
(
  @i_entityattributetypeid int,
  @i_relationid int,
  @i_data xml
)
as

  if ( @i_data is null )
  begin

    delete aom.RelationAttributeXml
    where RelationAttributeTypeId = @i_entityattributetypeid
      and RelationId = @i_relationid

    return @@error

  end

  update aom.RelationAttributeXml
  set Data = @i_data
  where RelationAttributeTypeId = @i_entityattributetypeid
    and RelationId = @i_relationid

  if ( @@rowcount = 0 )

    insert into aom.RelationAttributeXml ( RelationAttributeTypeId, RelationId, Data )
    values ( @i_entityattributetypeid, @i_relationid, @i_data )

  return @@error

go


use master
go

-- exec sp_detach_db @dbname = 'AomDB'

